package business.database.newcorps;

import java.util.List;
import org.apache.log4j.Logger;
import business.domainUtils.Context;
import dao.Center;
import business.ViewLogger;
import business.coding.CandidateCenter;
import business.database.Database;

/**
 * Acceso a la tabla de poscodecorpSector
 */
public class PosCodeCorpSectorDAO {
	
	private Logger _log;
	
	public PosCodeCorpSectorDAO() {
		_log = Logger.getLogger(PosCodeCorpSectorDAO.class);
	}
	/**
	* Crea la tabla PosCodeCorpSector que es la tabla final para los corps codificados a sector
	*/
   public void createPosCodeSectorTable(){
		 //se crea la tabla de direcciones codificadas a sector si no existe
		 try {
			 Database.getInstance().sqlnewcorps.execute("create table PosCodeCorpSector (idcodecorp integer, corp varchar(255), " +
				 "ISO varchar(5)," +
				 "PROVINCIA varchar(2)," +
				 "INSTIT varchar(4)," +
				 "ORGANISMO varchar(6)," +
				 "DPTO varchar(8)," +
				 "SECTOR varchar(8))")
		 } catch (Exception e) {
			 _log.warn ("No se ha podido crear la tabla PosCodeCorpSector. Probablemente ya exista.");
		 }
   }
   
   /**
    * Identificador maximo idcodecorp de esta tabla
    * @return
    */
	public Integer selectMaxidCodeCorp(){
		
		//select max(idcodecorp) from (SELECT idcodecorp FROM corp union select idcodecorp from poscodecorps union select idcodecorp from precodecorp)
		String query = "Select max(idcodecorp) as MAXID from PosCodeCorpSector"
		def maxid = Database.getInstance().sqlnewcorps.firstRow(query)
		if ((maxid != null) && (maxid[0] != null))
			return maxid[0]
		else return 0
	}
	
		
	/**
	* Inserta o actualiza la codificacion  a sector del corp
	* @param corp direccion del centro
	* @param cen centro codificado a sector
	*/
	
	public boolean insertOrUpdate(int idcodecorp, String corp, Center cen) {
			
		boolean ok = false;
			try {
				
				//Busco si el CodeCorp con ese piod ya existe
				
				def existcodecorp = Database.getInstance().sqlnewcorps.firstRow("select * from PosCodeCorpSector where idcodecorp = ${idcodecorp}")
				
				//Si no existe lo insertamos
				if (existcodecorp == null) {
					
					Database.getInstance().getSqlnewcorps().execute("INSERT INTO PosCodeCorpSector VALUES (" + idcodecorp + ", '"
							+ corp + "', '"
							+ cen.getISO()+ "', '"
							+ cen.getPROVINCIA()+ "', '"
							+ cen.getINSTIT()+ "', '"
							+ cen.getORGANISMO()+ "', '"
							+ cen.getDPTO()+ "', '"
							+ cen.getSECTOR()+ "')");
						
				} else {
					//Si ya existe actualizamos las columnas proporcionadas en Center dejando las que no aparezcan igual
					String sqlup = "UPDATE PosCodeCorpSector SET "
					
					if (cen.getISO() != "") sqlup += "ISO = '${cen.getISO()}', "
					if (cen.getPROVINCIA() != "") sqlup += "PROVINCIA = '${cen.getPROVINCIA()}', "
					if (cen.getINSTIT() != "") sqlup += "INSTIT = '${cen.getINSTIT()}', "
					if (cen.getORGANISMO() != "") sqlup += "ORGANISMO = '${cen.getORGANISMO()}', "
					if (cen.getDPTO() != "") sqlup += "DPTO = '${cen.getDPTO()}', "
					if (cen.getSECTOR() != "") sqlup += "SECTOR = '${cen.getSECTOR()}', "
					
					sqlup += "WHERE idcodecorp = ${idcodecorp}"
					sqlup = sqlup.replaceAll(", WHERE", " WHERE");
					
					Database.getInstance().getSqlnewcorps().execute(sqlup)
				}
				
				ok = true;
			} catch (Exception e) {
				ViewLogger.getInstance().error("Error al insertar un codigo para " + corp);
			}
		return ok;
	}

	/**
	* Obtiene la codificacion a sector de un idcodecorp. Asigna contexto y permatch de 100 para que aparezca el primero
	* @param idcodecorp
	* @return listado de centros a sector, pi o po
	*/
   public List<CandidateCenter> getPosCodeCorpsSectorCenters(int idcodecorp) {
	   
	   List<CandidateCenter> censW = new ArrayList<CandidateCenter>();
	   
	   Database.getInstance().sqlnewcorps.eachRow ("select * from PosCodeCorpSector where idcodecorp = ${idcodecorp}" , {
		   int id = it[0]
		   String corp = it[1]
		   String iso = it[2]
		   String prov = it[3]
		   String instit = it[4]
		   String org = it[5]
		   String dpto = it[6]
		   String sector = it[7]
		   Center cen = new Center(iso,prov, instit, org, dpto, sector);
		   cen.setIdCe idcodecorp
		   int range = Context._100;
		   double permatch = 100;
		   CandidateCenter cenW = new CandidateCenter();
		   cenW.setCenter cen
		   cenW.set_context_by_range range
		   cenW.set_permatch permatch
		   
		   censW.add(cenW)
	   })
	   
	   return censW;
   }
   
}
